import json
import re
import time
from DataProcessing.DatabaseProcessing import DataControl
from Extend.LocalError import LocalSqlServerError
from Extend.Log import LogMethod
from Extend.SqlHelper import SqlServer
from Extend.NoSql import Heart
import gevent
from Extend.NoSql import ImosChange
import datetime


class TargetDatabaseServer:
    def __init__(self, server, user, password, db):
        self.server = server
        self.db = SqlServer(self.server, user, password, db)
        self.var_change = VariableChange(self.db)
        self.wire_frame = WireFrameChange(self.db)

    def __str__(self):
        return self.server


class DataMethod:

    @classmethod
    def dataToList(cls, data: list):
        data = cls.dataToDict(data)
        new_dict = {}
        for column, value in data.items():
            temp = ''
            if column == 'DATE_LASTCHANGE':
                value = [min(value)]
            else:
                value = set(value)
            for x in value:
                temp += cls.valueToSQLChar(x) + ","
            new_dict[column] = temp.rstrip(',')
        return new_dict

    @staticmethod
    def valueToSQLChar(value):
        if type(value) is datetime.datetime:
            result = "'" + str(value)[0:str(value).rfind('.')] + "'"
        elif type(value) is int or type(value) is float:
            result = str(value)
        elif type(value) is bool:
            result = str(int(value))
        else:
            if re.search(r"[\u4e00-\u9fa5]", str(value)):
                result = "N'" + str(value) + "'"
            else:
                result = "'" + str(value) + "'"
        return result

    @classmethod
    def dataToDict(cls, data: list):
        new_dict = {}
        loop = 0
        for x in data:
            for m, n in x.items():
                if loop == 0:
                    new_dict[m] = [n]
                else:
                    new_dict[m].append(n)
            loop += 1
        return new_dict

    @classmethod
    def removeEmpty(cls, data: dict):
        remove_key = []
        for column, values in data.items():
            if values:
                continue
            remove_key.append(column)
        for x in remove_key:
            del data[x]


class VariableChange:
    # 需要查询的表
    tables = {'anglzone': ['DATE_LASTCHANGE', 'NAME'], 'IMOS': ["DATE_LASTCHANGE", "NAME"],
              'articles': ['DATE_LASTCHANGE', 'NAME'], 'transvar': ['DATE_LASTCHANGE', 'CODEWERT', 'FUER_VAR'],
              'KT_FOLDER': ['NAME'], 'anglprim': ['DATE_LASTCHANGE', 'NAME'], 'anglelem': ['DATE_LASTCHANGE', 'NAME'],
              'CSIDE': ['DATE_LASTCHANGE', 'NAME'], 'BBFOLDER': ['NAME'], 'BATCHBEM': ['NAME']
              }
    # ['FAVORITES','TABLESTAT','USERS' ]
    # 需要新增ID序号的表
    need_remove_id = {'KT_FOLDER': "DIR_ID", 'BBFOLDER': 'DIR_ID'}
    # 需要删除源数据的表,对应键
    need_delete = {'articles': ["NAME", "INORDER"], 'anglprim': ["NAME", "INORDER"],
                   'anglzone': ['NAME', 'INORDER', 'TREEID'], 'anglelem': ['NAME', 'INORDER', 'TREEID'],
                   'CSIDE': ['NAME', 'INORDER'], 'IMOS': ['ORDERID', 'NAME', 'TYP'],
                   'BBFOLDER': ['TYPE', 'NAME', 'PARENT_ID'], 'BATCHBEM': ['NAME', 'INORDER'],
                   'KT_FOLDER': ['TYPE', 'NAME', 'PARENT_ID'], "transvar": ["CODEWERT", "FUER_VAR"]
                   }
    # 附加条件
    additional = {'IMOS': "isnull([ORDERID],'') = ''"}
    # 删除表的主要条件
    remove_table_key = {'anglzone': 'NAME', 'articles': 'NAME', 'KT_FOLDER': 'NAME', 'anglprim': 'NAME',
                        'anglelem': 'NAME', 'CSIDE': 'NAME'}
    other_remove_dic = {"IMOS": ["NAME"], "transvar": ["CODEWERT", "FUER_VAR"]}

    def __init__(self, database: SqlServer):
        self.db = database

    def getSource(self, need_time, now):
        sql = "SELECT [NAME],[NAME] [FUER_VAR],[NAME2] [CODEWERT],EVENTTYPE " \
              "FROM [DATACHANGELOG] where [EVENTDATE] >= '{}' and [EVENTDATE] < '{}'  and [TYPE] != 173 " \
              "order by [EVENTDATE]".format(need_time, now)
        return self.db.getAllData(sql)

    def getData(self, new_line: list):
        result = {}
        for new_dict in new_line:
            for field, value in new_dict.items():
                new_dict[field] = DataMethod.valueToSQLChar(value)
            for table, columns in self.tables.items():
                if table not in result.keys():
                    result[table] = []
                sql = self.toSql(table, columns, new_dict)
                result[table].append(sql)
        for table in self.tables.keys():
            result[table] = self.db.getSqlAll(result[table])
        DataMethod.removeEmpty(result)
        if result:
            AdvancedDataOperations.removeIdentity(self, result)
        return result

    def getMaxDirId(self):
        key = list(self.need_remove_id.keys())[0]
        sql = "select max({}) id from {} ".format(self.need_remove_id[key], key)
        return self.db.getOneData(sql)['id']

    def toSql(self, table: str, columns: list, data_dic: dict):
        sql = "select * from {} where {}".format(table, '{}')
        condition = ''
        for column in set(columns) & set(data_dic.keys()):
            if column == 'DATE_LASTCHANGE':
                condition += "[%s] >= %s and " % (column, data_dic[column])
            else:
                condition += "[%s] = %s and " % (column, data_dic[column])
        if table in self.additional.keys():
            condition += self.additional[table]
        condition = condition.rstrip('and ')
        return sql.format(condition)

    def toRemoveSql(self, table: str, columns: list, data_dic: dict):
        sql = "delete from {} where {}".format(table, '{}')
        condition = ''
        for column in columns:
            if column == 'DATE_LASTCHANGE':
                continue
            condition += "[%s] = (%s) and " % (column, data_dic[column])
        if table in self.additional.keys():
            condition += self.additional[table]
        condition = condition.rstrip('and ')
        return sql.format(condition)

    def removeData(self, remove_list: list):
        sql_list = []
        sql = "delete from [{}] where [{}] = {}"
        for table, column in self.remove_table_key.items():
            for name_list in remove_list:
                sql_list.append(sql.format(table, column, DataMethod.valueToSQLChar(name_list[column])))
        return sql_list

    # 这个类是影响模块的类，包括变量
    def removeOthersTablesValue(self, u_data: list):
        sql_list = []
        for table, columns in self.other_remove_dic.items():
            for row_data in u_data:
                sql = "delete from [{}] where ".format(table)
                for column in columns:
                    sql += "[{}] = {} and ".format(column, DataMethod.valueToSQLChar(row_data[column]))
                if table in self.additional.keys():
                    sql += self.additional[table]
                    sql_list.append(sql.rstrip('and '))
        return sql_list


# 这个类是线框影响的类
class WireFrameChange(VariableChange):
    tables = {'CONTELEMFOLDER': ['NAME'], 'contelem': ['DATE_LASTCHANGE', 'CNAME']}
    need_delete = {'contelem': ["CNAME", "INORDER", 'ID', 'CONTTYPE'], 'CONTELEMFOLDER': ["TYPE", "NAME", "PARENT_ID"]}
    need_remove_id = {'CONTELEMFOLDER': "DIR_ID"}
    remove_table_key = {'CONTELEMFOLDER': 'NAME', 'contelem': 'CNAME'}

    def getSource(self, need_time, now):
        sql = "SELECT [CNAME],[CNAME] [NAME],min([DATE_LASTCHANGE]) [DATE_LASTCHANGE] " \
              " from [contelem] where [DATE_LASTCHANGE] >= '%s' and [DATE_LASTCHANGE] < '%s' group by [CNAME]" \
              % (need_time, now)
        return self.db.getAllData(sql)

    def getMeAllName(self):
        table = "CONTELEMFOLDER"
        sql = """select [{0}] from [{1}] group by [{0}]""".format(self.remove_table_key[table], table)
        result = self.db.getAllData(sql)
        return [x[self.remove_table_key[table]] for x in result]

    def removeData(self, name_list: set):
        new_list = [DataMethod.valueToSQLChar(value) for value in name_list]
        sql_list = []
        sql = "delete from [{}] where [{}] in ({})"
        for table, column in self.remove_table_key.items():
            sql_list.append(sql.format(table, column, ','.join(new_list)))
        return list(set(sql_list))

    def getData(self, new_dict: dict):
        result = {}
        for key in list(new_dict.keys()):
            new_dict[key] = DataMethod.valueToSQLChar(new_dict[key])
        for table, columns in self.tables.items():
            sql = self.toSql(table, columns, new_dict)
            result[table] = self.db.getAllData(sql)
        # 清除空数据，清除自增
        DataMethod.removeEmpty(result)
        if result:
            AdvancedDataOperations.removeIdentity(self, result)
        return result


class AdvancedDataOperations:

    @classmethod
    def removeIdentity(cls, server, sql_data: dict):
        for table, column in server.need_remove_id.items():
            if table in sql_data.keys():
                for value in sql_data[table]:
                    del value[column]

    @classmethod
    def sqlDataToSql(cls, sql_data: dict):
        sql_list = []
        for table, values in sql_data.items():
            for row_data in values:
                field = ''
                s_value = ''
                for column, value in row_data.items():
                    field += '[' + column + '],'
                    s_value += DataMethod.valueToSQLChar(value) + ","
                sql_list.append("insert into [{}]({}) values({})".format(table, field.rstrip(','), s_value.rstrip(',')))
        return sql_list

    @classmethod
    def addDataValues(cls, sql_data: dict):
        sql_list = cls.sqlDataToSql(sql_data)
        return sql_list

    @classmethod
    def removeTableValue(cls, object_method, all_sql_data: dict):
        sql_list = []
        for table, column in object_method.need_delete.items():
            if table not in all_sql_data.keys():
                continue
            for value_row in all_sql_data[table]:
                sql = "delete from [{}] where ".format(table)
                for row_column in column:
                    sql += "[{}] = {} and ".format(row_column, DataMethod.valueToSQLChar(value_row[row_column]))
                sql_list.append(sql.rstrip('and '))
        return set(sql_list)


class IMOSChangeControl(DataControl):

    @staticmethod
    def _addDataServer(server_dict):
        server = server_dict['Server']
        user = server_dict['User']
        password = server_dict['Password']
        db = server_dict['DataBase']
        database = TargetDatabaseServer(server, user, password, db)
        return database

    def recoveryServerToRun(self):
        while self.recovery_server:
            server = self.recovery_server.pop()
            self.target_servers.append(server)
            LogMethod.addLog([18, "服务器:%s 数据库已经恢复" % server.server, ""])

    # 循环主逻辑
    def startMonitors(self):
        while Heart.getMeHeart(self.__class__.__name__, ):
            Heart.setMeStat(self.__class__.__name__, True)
            if Heart.getMeHeart(self.__class__.__name__, ) is False:
                # print('检测到关闭')
                Heart.setMeStat(self.__class__.__name__, False)
                return
            if len(self.recovery_server) > 0:
                try:
                    self.recoveryServerToRun()
                    continue
                except:
                    pass
            self.mainMethod()
            gevent.sleep(10)
        Heart.setMeStat(self.__class__.__name__, False)
        # print('结束关闭')

    def wireFrame(self, server):
        need_time = ImosChange.getMeTime(server.server)
        now = datetime.datetime.now().strftime("%F %T")
        sql_list = []
        if need_time is None:
            need_time = now
        sql_data = self.local_server.wire_frame.getSource(need_time, now)
        # 更改数据
        if sql_data:
            for new_dict in sql_data:
                all_data = self.local_server.wire_frame.getData(new_dict)
                # 删除本地可能数据
                sql_list += AdvancedDataOperations.removeTableValue(WireFrameChange, all_data)
                # 更新添加本地数据
                sql_list += AdvancedDataOperations.addDataValues(all_data)
            # 因为删除会有其他意想不到的问题，因此删除
            # local_now_list = self.local_server.wire_frame.getMeAllName()
            # target_now_list = server.wire_frame.getMeAllName()
            # difference = set(target_now_list) - set(local_now_list)
            # if difference:
            #     sql_list += server.wire_frame.removeData(difference)
            new_sql_list = self.sqlSorted(sql_list)
            server.db.commitAll(new_sql_list)
        ImosChange.setMeTime(server.server, now)

    @staticmethod
    def sqlSorted(sql_list: list):
        all_sql_list = []
        remove_list = []
        not_remove_list = []
        for x in sql_list:
            if x.lower().startswith('delete'):
                remove_list.append(x)
                all_sql_list += list(set(not_remove_list))
                not_remove_list = []
            else:
                not_remove_list.append(x)
                all_sql_list += list(set(remove_list))
                remove_list = []
        return all_sql_list

    def changeVar(self, server: TargetDatabaseServer):
        need_time = ImosChange.getServerTime(server.server)
        now = datetime.datetime.now().strftime("%F %T")
        if need_time is None:
            need_time = now
        sql_data = self.local_server.var_change.getSource(need_time, now)
        # 更改数据
        if sql_data:
            all_list = []
            not_delete_list = []
            delete_list = []
            for row in sql_data:
                if row['EVENTTYPE'] != 3:
                    if delete_list:
                        all_list += self.deleteToSql(delete_list)
                    not_delete_list.append(row)
                else:
                    if not_delete_list:
                        all_list += self.notDeleteToSql(not_delete_list, need_time)
                    delete_list.append(row)
            else:
                if not_delete_list:
                    all_list += self.notDeleteToSql(not_delete_list, need_time)
                if delete_list:
                    all_list += self.deleteToSql(delete_list)
            new_sql_list = self.sqlSorted(all_list)
            server.db.commitAll(new_sql_list)
        ImosChange.setServerTime(server.server, now)

    def cleanList(self, content_list: list):
        new_list = []
        for content in content_list:
            del content['EVENTTYPE']
            new_list.append(json.dumps(content))
        not_repetition_list = set(new_list)
        content_list.clear()
        return [json.loads(x) for x in not_repetition_list]

    def notDeleteToSql(self, not_delete_list, need_time):
        sql_list = []
        not_repetition_list = self.cleanList(not_delete_list)
        for row in not_repetition_list:
            row['DATE_LASTCHANGE'] = need_time
        all_data = self.local_server.var_change.getData(not_repetition_list)
        # 删除本地可能数据
        sql_list += AdvancedDataOperations.removeTableValue(VariableChange, all_data)
        # 更新添加本地数据
        sql_list += AdvancedDataOperations.addDataValues(all_data)
        return sql_list

    def deleteToSql(self, delete_list):
        not_repetition_list = self.cleanList(delete_list)
        sql_list = self.local_server.var_change.removeData(not_repetition_list)
        sql_list += self.local_server.var_change.removeOthersTablesValue(not_repetition_list)
        return sql_list

    def removeServer(self, server, error):
        if server is self.local_server:
            raise LocalSqlServerError(error)
        for i in range(len(self.target_servers)):
            if self.target_servers[i] is server:
                self.target_servers.pop(i)
                break
        LogMethod.addLog([17, "服务器:%s 无法连接数据库或无法读取对应表内容" % server.server, str(error)])
        gevent.spawn(MonitorDataBaseServer.heartbeatDetection, server, self._recoveryServer)

    def mainMethod(self):
        for server in self.target_servers:
            try:
                # 变更
                self.changeVar(server)
                # 线框增加
                self.wireFrame(server)
            except Exception as err:
                try:
                    self.removeServer(server, err)
                except LocalSqlServerError as error:
                    LogMethod.addLog([15, "本地数据库异常", str(error)])
                    Heart.setMeStat(self.__class__.__name__, False)
                    Heart.setMeHeart(self.__class__.__name__, False)
                    return
                except Exception as error:
                    LogMethod.addLog([16, "未知异常，具体错误请查看详情", str(error)])


class MonitorDataBaseServer:

    @staticmethod
    def heartbeatDetection(obj: TargetDatabaseServer, return_func, loop=0):
        while True:
            try:
                obj.var_change.getMaxDirId()
                break
            except Exception as e:
                loop += 1
                if loop <= 50:
                    time.sleep(5)
                elif loop <= 100:
                    time.sleep(10)
                else:
                    time.sleep(20)
        return_func(obj)


if __name__ == '__main__':
    dc = IMOSChangeControl({'Server': '172.16.200.252', 'User': 'sa', 'Password': '2020', 'DataBase': 'LWJ'},
                           [{'Server': '172.16.200.253', 'User': 'sa', 'Password': '2020',
                             'DataBase': 'LWJ'}])  # 2删除不添加
    dc.start()
